class MigrateInitial < ActiveRecord::Migration

  class << self
    #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    # Database Create / Drop methods
    #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    def up
      #
      #DB_USER Must be created *BEFORE* this script can be run  
      #
      create_updaters_table
      create_change_types_table
      create_contact_formats_table
      create_addresses_table
    end

    def down
      drop_addresses_table
      drop_contact_formats_table
      drop_change_types_table
      drop_updaters_table
    end


    private
      #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      # Table create/drop methods
      #~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  
      def create_updaters_table
        create_table_with_privies(:updaters, :force => true) do |t|
          t.column("name", :text)
          t.column("user_id", :integer)
        end
      end

      def drop_updaters_table
        execute("select setval('updaters_id_seq',1,false)")
        drop_table(:updaters)
      end

      def create_change_types_table
        create_table_with_privies(:change_types, :force => true) do |t|
          t.column("code", :string, :limit => 1, :null => false)
          t.column("name", :string, :limit => 20, :null => false)
        end

        add_index(:change_types, ["code"], :name => "change_type_code_key", :unique => true)

        ChangeType.create(:code => 'C', :name => 'Change')
        ChangeType.create(:code => 'U', :name => 'Update')
        ChangeType.create(:code => 'D', :name => 'Delete')
        ChangeType.create(:code => 'A', :name => 'Add Association')
        ChangeType.create(:code => 'R', :name => 'Remove Association')
      end

      def drop_change_types_table
        execute("select setval('change_types_id_seq',1,false)")
        drop_table(:change_types)
      end

      def create_contact_formats_table
        create_table_with_privies("contact_formats", :force => true) do |t|
          t.column("name", :string, :limit => 10)
          t.column("format_rule", :text, :null => false)
        end

        ContactFormat.create(:name => 'Phone', 
          :format_rule => '^\\D?(\\d{3})\\D?\\D?(\\d{3})\\D?(\\d{4})$')
        ContactFormat.create(:name => 'Email', 
          :format_rule => '^[\\w\\-\\+\\&\\*]+(?:\\.[\\w\\-\\+\\&\\*]+)*@(?:[\\w-]+\\.)+[a-zA-Z]{2,7}$')
      end

      def drop_contact_formats_table
        execute("select setval('contact_formats_id_seq',1,false)")
        drop_table(:contact_formats)
      end

      def create_addresses_table
        create_table_with_history(:addresses, :force => true) do |t|
          t.column("line_one", :text, :null => false)
          t.column("line_two", :text)
          t.column("city", :text, :null => false)
          t.column("state_cd", :string, :limit => 2)
          t.column("zip_code", :string, :limit => 10, :null => false)
          t.column("updated_by", :integer, :null => false)
          t.column("updated_at", :datetime)
        end
      end

      def drop_addresses_table
        execute("select setval('addresses_id_seq',1,false)")
        drop_table_with_history(:addresses)
      end

  end#end class block
end
